<?php
/**
 * Zugriff auf eine City-Tabelle
 *
 * @author		Joachim Ruf
 * @copyright	Copyright (c) 2003-2013 Loresoft Software (http://www.loresoft.de)
 * @license		http://yapary.loresoft.de/lizenzbedingungen     New BSD License
 *
 * */


Class Yapary_City_CityList
{
    /** @var Object $oSql			: Sql-Klasse fuer Datenbankzugriff */
    private $oSql        = NULL;
    /** @var string $dbTableName	: Name der Datenbanktabelle */
    private $dbTableName  = 'yapary_city_city';


    /**
     * @param Object $_oSql	: Datenbankklasse fuer Datenbankzugriff
     * */
    function __construct (Yapary_Db_Sql_Connector $_oSql)
    {
        $this->oSql = $_oSql;
    }


    /**
     * Fuegt einen neuen Datensatz hinzu
     * @return Yapary_City_City|NULL
     * */
    public function add ($zip)
    {
        $oDbResult = $this->oSql->request('INSERT INTO '.$this->dbTableName.' (Zip) VALUES (:zip)',
                                 array('zip' => $zip));

        return new Yapary_City_City($this->oSql, $this->dbTableName, $oDbResult->getLastInsertId());
    }


    /**
     * Loescht einen existierenden Datensatz
     * @return bool
     * */
    public function delete ($zip)
    {
        $oDbResult = $this->oSql->request('DELETE
                					  FROM '.$this->dbTableName.'
                					  WHERE Zip=:zip',
                                     array('zip' => $zip));
        return true;
    }


    /**
     * Liefert alle Staedte
     * @return Yapary_City_City
     * */
    public function get ($zip)
    {
        return new Yapary_City_City($this->oSql, $zip);
    }


    /**
     * Liefert alle Staedte anhand der Postleitzahl per LIKE
     * @return Array[Yapary_City_City]
     * */
    public function getListByZip ($zip, $orderBy = 'Zip')
    {
        $arrResult = array();
        $orderBy = (preg_match('/^([a-zA-Z-_ ]+)([a-zA-Z-_0-9 ]*)$/', $orderBy)) ? ' ORDER BY '.$orderBy : '';

        $oDbResult = $this->oSql->request('SELECT *
        					  FROM '.$this->dbTableName.'
        					  WHERE Zip LIKE :zip'.$orderBy,
                            array('zip' => $zip.'%'));

        if ($oDbResult->getCount() >= 1) {
            while ($row = $oDbResult->getNext()) {
                $arrResult[] = new Yapary_City_City($this->oSql, $row);
            }
        }

        return $arrResult;
    }


    /**
     * Liefert alle Staedte anhand des Stadtnamens per LIKE
     * @return [Yapary_City_City]
     * */
    public function getListByCity ($name, $orderBy = 'Zip')
    {
        $arrResult = array();
        $orderBy = (preg_match('/^([a-zA-Z-_ ]+)([a-zA-Z-_0-9 ]*)$/', $orderBy)) ? ' ORDER BY '.$orderBy : '';

        $oDbResult = $this->oSql->request('SELECT *
        					  FROM '.$this->dbTableName.' t1
        					  WHERE City LIKE :city'.$orderBy,
                            array('city' => $name.'%'));

        if ($oDbResult->getCount() >= 1) {
            while ($row = $oDbResult->getNext()) {
                $arrResult[] = new Yapary_City_City($this->oSql, $row);
            }
        }

        return $arrResult;
    }


    /**
     * Liefert alle Staedte
     * @return Array[Yapary_City_City]
     * */
    public function getListByFederalState ($federalStateId, $orderBy = 'City')
    {
        $arrResult = array();
        $orderBy = (preg_match('/^([a-zA-Z-_ ]+)([a-zA-Z-_0-9 ]*)$/', $orderBy)) ? ' ORDER BY '.$orderBy : '';

        $oDbResult = $this->oSql->request('SELECT *
        					  FROM '.$this->dbTableName.' t1
        					  WHERE FederalState=:id'.$orderBy,
                            array('id' => $federalStateId));

        if ($oDbResult->getCount() >= 1) {
            while ($row = $oDbResult->getNext()) {
                $arrResult[] = new Yapary_City_City($this->oSql, $row);
            }
        }

        return $arrResult;
    }


    /**
     * Liefert die Anzahl aller Datensaetze
     * @return int
     * */
    public function getCount ()
    {
        $oDbResult = $this->oSql->request('SELECT COUNT(*) AS Counter
        					  FROM '.$this->dbTableName.'',
                             array());

        $row = $oDbResult->getNext();
        return (int) $row->Counter;
    }


}


?>